--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Data Schema For Version 0.0.0.0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryDelete]
(
	@Original_EmployeeID Int,
	@Original_TerritoryID NVarChar(20)
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[EmployeeTerritories] 
WHERE 
	[EmployeeID] = @Original_EmployeeID AND
	[TerritoryID] = @Original_TerritoryID ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByEmployeeTerritoryPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritoryPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritoryPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM 
[EmployeeTerritories]
WHERE

	[EmployeeTerritories].[EmployeeID] IN (SELECT [EmployeeID] 
											FROM OpenXML(@hDoc, '//EmployeeTerritories', 2) 
											WITH ([EmployeeID] char(36) 'EmployeeID')) 

	AND

	[EmployeeTerritories].[TerritoryID] IN (SELECT [TerritoryID] 
											FROM OpenXML(@hDoc, '//EmployeeTerritories', 2) 
											WITH ([TerritoryID] char(36) 'TerritoryID')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByEmployeeTerritorySinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritorySinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritorySinglePk]
(
	@EmployeeID Int,
	@TerritoryID NVarChar(20)
)
AS

SELECT 
	[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM
[EmployeeTerritories]
WHERE 
[EmployeeTerritories].[EmployeeID] = @EmployeeID AND [EmployeeTerritories].[TerritoryID] = @TerritoryID 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryInsert]
(
	@TerritoryID NVarChar(20) = default,
	@EmployeeID Int = default,
	@created_date datetime,
	@created_by varchar (50),
	@modified_by varchar (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()

INSERT INTO [EmployeeTerritories]
(
[EmployeeID],
[TerritoryID],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@EmployeeID,
	@TerritoryID,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);


SELECT 
	[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM
[EmployeeTerritories]
WHERE
	[EmployeeTerritories].[EmployeeID] = @EmployeeID AND
	[EmployeeTerritories].[TerritoryID] = @TerritoryID;
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryPagingSelect]
(
	@page int, -- page number selected by the user
	@pageSize int, -- number of items on the page
	@orderByColumn varchar(100), -- name of column to order things by
	@ascending bit, -- order column ascending or descending
	@filter varchar(100) = null, -- filter statement passed in to determine like criteria on order by column
	@count int out -- number of items in the collection
)
AS

SET NOCOUNT ON;


CREATE TABLE #tmpTable
(
[EmployeeID] Int,
[TerritoryID] NVarChar(20)
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[employeeid],
	[territoryid]
)
SELECT
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[territoryid]
FROM
[EmployeeTerritories]
WHERE
	(@orderByColumn = 'TerritoryID' and (((@filter is null) or ([EmployeeTerritories].[TerritoryID] is null)) or (@filter is not null and [EmployeeTerritories].[TerritoryID] LIKE @filter)))
or
	(@orderByColumn = 'EmployeeID' and (((@filter is null) or ([EmployeeTerritories].[EmployeeID] is null)) or (@filter is not null and [EmployeeTerritories].[EmployeeID] = @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[EmployeeTerritories]
WHERE
	(@orderByColumn = 'TerritoryID' and (((@filter is null) or ([EmployeeTerritories].[TerritoryID] is null)) or (@filter is not null and [EmployeeTerritories].[TerritoryID] LIKE @filter)))
or
	(@orderByColumn = 'EmployeeID' and (((@filter is null) or ([EmployeeTerritories].[EmployeeID] is null)) or (@filter is not null and [EmployeeTerritories].[EmployeeID] = @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [EmployeeTerritories] ON #tmpTable.[employeeid] = [EMPLOYEETERRITORIES].[employeeid] AND
#tmpTable.[territoryid] = [EMPLOYEETERRITORIES].[territoryid]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectBySearchAnd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectBySearchAnd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectBySearchAnd]
(
	@TerritoryID NVarChar(20) = null, 
	@EmployeeID Int = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[EmployeeID] Int,
[TerritoryID] NVarChar(20)
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[employeeid],
	[territoryid]
)
SELECT
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[territoryid]
FROM
[EmployeeTerritories]
WHERE
((@TerritoryID is null) OR ([EmployeeTerritories].[TerritoryID] LIKE @TerritoryID)) AND
((@EmployeeID is null) OR ([EmployeeTerritories].[EmployeeID] = @EmployeeID))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[EmployeeTerritories]
WHERE
((@TerritoryID is null) OR ([EmployeeTerritories].[TerritoryID] LIKE @TerritoryID)) AND
((@EmployeeID is null) OR ([EmployeeTerritories].[EmployeeID] = @EmployeeID))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [EmployeeTerritories] ON #tmpTable.[employeeid] = [EMPLOYEETERRITORIES].[employeeid] AND
#tmpTable.[territoryid] = [EMPLOYEETERRITORIES].[territoryid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryUpdate]
(
	@modified_by varchar (50),
	@modified_date datetime = null,
	@Original_EmployeeID Int,
	@Original_TerritoryID NVarChar(20),
	@Original_time_stamp timestamp

)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[EmployeeTerritories] 
SET
[modified_by] = @modified_by,
[modified_date] = @modified_date

WHERE
	[EmployeeTerritories].[EmployeeID] = @Original_EmployeeID AND
	[EmployeeTerritories].[TerritoryID] = @Original_TerritoryID AND
	[EmployeeTerritories].[time_stamp] = @Original_time_stamp


SELECT
	[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]
FROM 
[EmployeeTerritories]
WHERE
	[EmployeeTerritories].[EmployeeID] = @Original_EmployeeID AND
	[EmployeeTerritories].[TerritoryID] = @Original_TerritoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByCreatedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM
[EmployeeTerritories]
WHERE
((([EmployeeTerritories].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [EmployeeTerritories].[created_date])) AND 
((([EmployeeTerritories].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [EmployeeTerritories].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByModifiedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM
[EmployeeTerritories]
WHERE
((([EmployeeTerritories].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [EmployeeTerritories].[modified_date])) AND 
((([EmployeeTerritories].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [EmployeeTerritories].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectBySearchOr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectBySearchOr]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectBySearchOr]
(
	@TerritoryID NVarChar(20) = null, 
	@EmployeeID Int = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[EmployeeID] Int,
[TerritoryID] NVarChar(20)
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[employeeid],
	[territoryid]
)
SELECT
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[territoryid]
FROM
[EmployeeTerritories]
WHERE
((@TerritoryID is null) OR ([EmployeeTerritories].[TerritoryID] LIKE @TerritoryID)) OR
((@EmployeeID is null) OR ([EmployeeTerritories].[EmployeeID] = @EmployeeID))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[EmployeeTerritories]
WHERE
((@TerritoryID is null) OR ([EmployeeTerritories].[TerritoryID] LIKE @TerritoryID)) OR
((@EmployeeID is null) OR ([EmployeeTerritories].[EmployeeID] = @EmployeeID))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [EmployeeTerritories] ON #tmpTable.[employeeid] = [EMPLOYEETERRITORIES].[employeeid] AND
#tmpTable.[territoryid] = [EMPLOYEETERRITORIES].[territoryid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TerritoryID' THEN [EmployeeTerritories].[TerritoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [EmployeeTerritories].[EmployeeID] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [EmployeeTerritories].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [EmployeeTerritories].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [EmployeeTerritories].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [EmployeeTerritories].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelect]
AS

SET NOCOUNT ON;

SELECT
		[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM 
[EmployeeTerritories]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByEmployeePks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM 
[EmployeeTerritories]
WHERE
	[EmployeeTerritories].[EmployeeID] IN (SELECT [EmployeeID]
				FROM OpenXML(@hDoc, '//Employees', 2)
				WITH ([EmployeeID] Int 'EmployeeID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByTerritoryPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByTerritoryPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByTerritoryPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[EmployeeTerritories].[territoryid],
	[EmployeeTerritories].[employeeid],
	[EmployeeTerritories].[created_by],
	[EmployeeTerritories].[created_date],
	[EmployeeTerritories].[modified_by],
	[EmployeeTerritories].[modified_date],
	[EmployeeTerritories].[time_stamp]

FROM 
[EmployeeTerritories]
WHERE
	[EmployeeTerritories].[TerritoryID] IN (SELECT [TerritoryID]
				FROM OpenXML(@hDoc, '//Territories', 2)
				WITH ([TerritoryID] NVarChar(20) 'TerritoryID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

